package com.example.world.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.example.world.domain.City;
import com.example.world.utils.JDBCUtils;

public class CityDAO {
	// 根据id查询
		public City findByCode(int id) throws SQLException
		{
			// 1. 连接 
			Connection conn = JDBCUtils.getConnection();
			// 2. 
			String sql = "select * from city where ID=?";
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			// 3. 
			ResultSet rs = stmt.executeQuery();
			City c = null;
		
			if(rs.next())
			{
				String code = rs.getString("CountryCode");
				String name = rs.getString("Name");
				String district = rs.getString("District");
				int population = rs.getInt("Population");
				c = new City(id,name,code,district,population);
			}
					
			return c;
		}
		
	//根据name查询
		public City findByName(String name) throws SQLException
		{
			// 1. 连接 
			Connection conn = JDBCUtils.getConnection();
			// 2. 
			String sql = "select * from city where Name=?";
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setString(1, name);
			// 3. 
			ResultSet rs = stmt.executeQuery();
			City c1 = null;
		
			if(rs.next())
			{
				int id = rs.getInt("ID");
				String code = rs.getString("CountryCode");
				String district = rs.getString("District");
				int population = rs.getInt("Population");
				c1 = new City(id,name,code,district,population);
			}
					
			return c1;
		}
		
	//根据name查询
		public City findByCountryCode(String countrycode) throws SQLException{
		// 1. 连接 
		Connection conn = JDBCUtils.getConnection();
		// 2. 
		String sql = "select * from city where CountryCode=?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, countrycode);
		// 3. 
		ResultSet rs = stmt.executeQuery();
		City c1 = null;
			if(rs.next()){
				int id = rs.getInt("ID");
				String name = rs.getString("name");
				String district = rs.getString("District");
				int population = rs.getInt("Population");
				c1 = new City(id,name,countrycode,district,population);
			}
							
			return c1;
		}
		
	// 查询所有
		public List<City> findAll() throws SQLException
		{
			// 1. 连接
			Connection conn = JDBCUtils.getConnection();
			// 2.
			String sql = "select * from city";
			PreparedStatement stmt = conn.prepareStatement(sql);
			// 3.
			ResultSet rs = stmt.executeQuery();
			List<City> list = new ArrayList<>();
			while (rs.next()) 
			{
				int id = rs.getInt("ID");
				String name = rs.getString("Name");
				String code = rs.getString("CountryCode");
				String district = rs.getString("District");
				int population = rs.getInt("Population");
				City c = new City(id, name, code,district,population);
				list.add(c);
			}

			return list;
		}
	}